package nckd.wnqjt.tmc.tda.plugin.form;

import kd.bos.algo.DataSet;
import kd.bos.algo.Row;
import kd.bos.dataentity.entity.DynamicObject;
import kd.bos.entity.report.AbstractReportListDataPlugin;
import kd.bos.entity.report.ReportQueryParam;
import kd.bos.logging.Log;
import kd.bos.logging.LogFactory;
import kd.bos.orm.query.QCP;
import kd.bos.orm.query.QFilter;
import kd.bos.servicehelper.BusinessDataServiceHelper;
import kd.bos.servicehelper.QueryServiceHelper;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author
 * @date
 * @desc 资金总额明细报表 复制
 */
public class TotalFundsDetailListDataCopyPlugin extends AbstractReportListDataPlugin {
    private static Log log = LogFactory.getLog(TotalFundsDetailListDataCopyPlugin.class);

    @Override
    public DataSet query(ReportQueryParam reportQueryParam, Object o) throws Throwable {
        Map<String, Object> customParam = reportQueryParam.getCustomParam();
        /**
         * ***************************************************************
         */
        //获取当前日期起止日期
        Date startdate;
        Date enddate;
        Date ninetyDate;
        if (customParam != null && customParam.get("nckd_querydate") != null) {
            String querydatestr = String.valueOf(customParam.get("nckd_querydate"));
            Date parseDate = null;
            try {
                parseDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(querydatestr);
            } catch (ParseException e) {
                throw new RuntimeException(e);
            }
            LocalDate localDate = LocalDate.from(LocalDateTime.ofInstant(parseDate.toInstant(), ZoneId.systemDefault()).toLocalDate());
            startdate = Date.from(localDate.atStartOfDay().atZone(ZoneId.systemDefault()).toInstant());
            LocalDateTime enddateTime = localDate.atTime(23, 59, 59);
            enddate = Date.from(enddateTime.atZone(ZoneId.systemDefault()).toInstant());
            LocalDateTime ninetyDateTime = enddateTime.plusDays(90);
            ninetyDate = Date.from(ninetyDateTime.atZone(ZoneId.systemDefault()).toInstant());
        } else {
            //获取当前日期起止日期
            startdate = Date.from(LocalDate.now().atStartOfDay().atZone(ZoneId.systemDefault()).toInstant());
            LocalDateTime enddateTime = LocalDate.now().atTime(23, 59, 59);
            enddate = Date.from(enddateTime.atZone(ZoneId.systemDefault()).toInstant());
            LocalDateTime ninetyDateTime = enddateTime.plusDays(90);
            ninetyDate = Date.from(ninetyDateTime.atZone(ZoneId.systemDefault()).toInstant());
        }
        log.info("startdate:" + startdate);
        log.info("enddate:" + enddate);
        log.info("ninetyDate:" + ninetyDate);
        //子公司当前余额+母公司当前余额 排除结算中心
        QFilter finorgtype = new QFilter("number", QCP.equals, "FI-015");
        DynamicObject queryOne = QueryServiceHelper.queryOne("bd_finorgtype", "id", finorgtype.toArray());

        //先获取符合条件的账户列表
        QFilter acctstatusfilter = new QFilter("acctstatus", QCP.not_equals, "closed")
                .and(new QFilter("bank.finorgtype.id", QCP.not_equals, queryOne.getLong("id")))
                .and("opendate", QCP.less_equals, enddate);
        String getAcctSelectedFieds = "id,company,openorg,issetbankinterface,bank.bank_cate bankbankcate,bankaccountnumber,isvirtual,name,currencyname,defaultcurrency,opendate,acctstatus,acctstyle,accttype,acctproperty,manager,acctname,finorgtype";
        DataSet acctDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "bd_accountbanks", getAcctSelectedFieds, acctstatusfilter.toArray(), null);
        log.info("acctDataSet:");
        acctDataSet.print(true);

        //子公司当前余额+母公司当前余额 排除结算中心
        QFilter beiBankbalanceFilter = new QFilter("bizdate", QCP.large_equals, startdate).and("bizdate", QCP.less_equals, enddate);
        if (queryOne != null) {
            Object pkValue = queryOne.getLong("id");
            beiBankbalanceFilter.and(new QFilter("bank.finorgtype.id", QCP.not_equals, pkValue));
        }
        String beiBankbalanceSelectField = "id,company.id companyid,company.number companynumber,company.name companyname" +
                ",accountbank.id accountbankid,accountbank.bankaccountnumber bankaccountnumber,bizdate,nckd_depositbalance,amount,freezebalance,datasource";
        DataSet beiBankbalanceDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "bei_bankbalance", beiBankbalanceSelectField, beiBankbalanceFilter.toArray(), null);
        log.info("beiBankbalanceDataSet:");
        beiBankbalanceDataSet.print(true);
        beiBankbalanceDataSet = beiBankbalanceDataSet.updateField("nckd_depositbalance", "case when nckd_depositbalance is null then 0 else nckd_depositbalance end");
        beiBankbalanceDataSet = beiBankbalanceDataSet.updateField("amount", "case when amount is null then 0 else amount end");
        log.info("更新后的beiBankbalanceDataSet:");
        beiBankbalanceDataSet.print(true);
        DataSet beiBankbalanceDataSetCopy = beiBankbalanceDataSet.copy();
//        Map<String, BigDecimal> depositbalanceMap = getParentBankAccountNumberAmount(beiBankbalanceDataSetCopy);
//
//        //List<String> accountList = Arrays.asList("1502209019300176348", "791902491410301", "14314101040028653", "36050153017100003058");
//        //排除母账户，母账户的资金总额=母账户的当前余额-所有子账户的上存余额
//        DataSet[] splitByFilter = beiBankbalanceDataSet.splitByFilter(new String[]{"bankaccountnumber in ('1502209019300176348','791902491410301','14314101040028653','36050153017100003058')"}, true);
//        String updateAmount = "case when bankaccountnumber = '1502209019300176348' then (amount - " + depositbalanceMap.get("1502209019300176348")
//                + ") when bankaccountnumber = '791902491410301' then (amount - " + depositbalanceMap.get("791902491410301")
//                + ") when bankaccountnumber = '14314101040028653' then (amount - " + depositbalanceMap.get("14314101040028653")
//                + ") when bankaccountnumber = '36050153017100003058' then amount - " + depositbalanceMap.get("36050153017100003058")
//                + " else 0 end";
//        log.info("获取银行母账户对应的子账户的更新语句" + updateAmount);
//        DataSet dataSet = splitByFilter[0].updateField("amount", updateAmount);
//        DataSet union1 = dataSet.union(splitByFilter[1]);

        DataSet parentAmount = getParentBankAccountNumberAmount(beiBankbalanceDataSetCopy);
        List<String> depositbalanceList = new ArrayList<>();
        DataSet parentAmountCopy = parentAmount.copy();
        while (parentAmountCopy.hasNext()) {
            Row next = parentAmountCopy.next();
            depositbalanceList.add(next.getString("parentcompanyaccount"));
        }

        //排除母账户，母账户的资金总额=母账户的当前余额-所有子账户的上存余额
        DataSet dataSet1 = beiBankbalanceDataSet.filter("bankaccountnumber in numbers", Collections.singletonMap("numbers", depositbalanceList));
        DataSet dataSet2 = beiBankbalanceDataSet.filter("bankaccountnumber not in numbers", Collections.singletonMap("numbers", depositbalanceList));
        dataSet1 = dataSet1.join(parentAmount).on("bankaccountnumber", "parentcompanyaccount").select(beiBankbalanceDataSet.getRowMeta().getFieldNames(), new String[]{"nckd_depositbalance depositbalance_sum"}).finish();
        dataSet1 = dataSet1.updateField("amount", "amount - depositbalance_sum");
        dataSet1 = dataSet1.removeFields("depositbalance_sum");
        DataSet union1 = dataSet1.union(dataSet2);

        //获取虚拟户
        DataSet filterAcctDataSet = acctDataSet.copy().filter("isvirtual = true");
        List<String> isvirtualAccountNoList = new ArrayList<>();
        while (filterAcctDataSet.hasNext()) {
            Row next = filterAcctDataSet.next();
            String bankaccountnumber = next.getString("bankaccountnumber");
            isvirtualAccountNoList.add(bankaccountnumber);
        }
        //受限资金管理
        String amRestrictedfundsmanagerSelectField = "id,org.id companyid,org.number companynumber,org.name companyname,bankacct.id accountbankid,bankacct.bankaccountnumber bankaccountnumber,unrestrictedamt";
        //增加受限
        QFilter businesstype = new QFilter("businesstype", QCP.equals, "1").and("billstatus", QCP.equals, "C")
                .and("bankacct.bankaccountnumber", QCP.in, isvirtualAccountNoList).and("restricteddate", QCP.less_equals, enddate)
                .and(new QFilter("estimatedliftdate", QCP.large_than, ninetyDate).or(QFilter.isNull("estimatedliftdate")));
        DataSet amRestrictedfundsmanagerDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "am_restrictedfundsmanager", amRestrictedfundsmanagerSelectField, businesstype.toArray(), null);
        log.info("amRestrictedfundsmanagerDataSet:");
        amRestrictedfundsmanagerDataSet.print(true);
        amRestrictedfundsmanagerDataSet = amRestrictedfundsmanagerDataSet.groupBy(new String[]{"companyid", "bankaccountnumber"}).sum("unrestrictedamt").finish();
        log.info("分组合并后的amRestrictedfundsmanagerDataSet:");
        amRestrictedfundsmanagerDataSet.print(true);
        //增加受限
        QFilter businesstype1 = new QFilter("businesstype", QCP.equals, "1").and("billstatus", QCP.equals, "C")
                .and("restricteddate", QCP.less_equals, enddate)
                .and(new QFilter("estimatedliftdate", QCP.large_than, ninetyDate).or(QFilter.isNull("estimatedliftdate")));
        DataSet amRestrictedfundsmanagerDataSet1 = QueryServiceHelper.queryDataSet(this.getClass().getName(), "am_restrictedfundsmanager", amRestrictedfundsmanagerSelectField, businesstype1.toArray(), null);
        log.info("amRestrictedfundsmanagerDataSet1:");
        amRestrictedfundsmanagerDataSet1.print(true);
        amRestrictedfundsmanagerDataSet1 = amRestrictedfundsmanagerDataSet1.groupBy(new String[]{"companyid", "bankaccountnumber"}).sum("unrestrictedamt").finish();
        log.info("分组合并后的amRestrictedfundsmanagerDataSet:");
        amRestrictedfundsmanagerDataSet1.print(true);
        //定期存款处理
        String cimDepositSelectField = "id,org.id companyid,org.number companynumber,org.name companyname,settleaccount.id accountbankid,settleaccount.bankaccountnumber bankaccountnumber,surplusamount";
        QFilter cimDepositQfilter = new QFilter("billstatus", QCP.equals, "C").and("finaccount", QCP.in, isvirtualAccountNoList).and("intdate", QCP.less_equals, enddate);
        DataSet cimDepositDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cim_deposit", cimDepositSelectField, cimDepositQfilter.toArray(), null);
        log.info("cimDepositDataSet:");
        cimDepositDataSet.print(true);
        cimDepositDataSet = cimDepositDataSet.groupBy(new String[]{"companyid", "bankaccountnumber"}).sum("surplusamount").finish();
        log.info("分组合并后的cimDepositDataSet:");
        cimDepositDataSet.print(true);
        QFilter cimDepositQfilter1 = new QFilter("billstatus", QCP.equals, "C")
                .and("intdate", QCP.less_equals, enddate);
        DataSet cimDepositDataSet1 = QueryServiceHelper.queryDataSet(this.getClass().getName(), "cim_deposit", cimDepositSelectField, cimDepositQfilter1.toArray(), null);
        log.info("cimDepositDataSet1:");
        cimDepositDataSet1.print(true);
        cimDepositDataSet1 = cimDepositDataSet1.groupBy(new String[]{"companyid", "bankaccountnumber"}).sum("surplusamount").finish();
        log.info("分组合并后的cimDepositDataSet1:");
        cimDepositDataSet1.print(true);
        DataSet finish1 = acctDataSet.leftJoin(union1).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
                .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "isvirtual", "finorgtype"}, new String[]{"nckd_depositbalance", "amount", "freezebalance"}).finish();
        DataSet finish2 = finish1.leftJoin(amRestrictedfundsmanagerDataSet).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
                .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "isvirtual", "finorgtype", "nckd_depositbalance", "amount", "freezebalance"}, new String[]{"unrestrictedamt"}).finish();
        DataSet finish3 = finish2.leftJoin(cimDepositDataSet).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
                .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "isvirtual", "finorgtype", "nckd_depositbalance", "amount", "freezebalance", "unrestrictedamt"}, new String[]{"surplusamount"}).finish();

        DataSet finish4 = finish3.leftJoin(amRestrictedfundsmanagerDataSet1).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
                .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "isvirtual", "finorgtype", "nckd_depositbalance", "amount", "freezebalance", "unrestrictedamt", "surplusamount"}, new String[]{"unrestrictedamt as unrestrictedamt1"}).finish();
        DataSet finish = finish4.leftJoin(cimDepositDataSet1).on("company", "companyid").on("bankaccountnumber", "bankaccountnumber")
                .select(new String[]{"company", "bankaccountnumber", "issetbankinterface", "bankbankcate", "isvirtual", "finorgtype", "nckd_depositbalance", "amount", "freezebalance", "unrestrictedamt", "surplusamount", "unrestrictedamt1"}, new String[]{"surplusamount as surplusamount1"}).finish();
        finish = finish.updateField("nckd_depositbalance", "case when nckd_depositbalance is null then 0 else nckd_depositbalance end");
        finish = finish.updateField("amount", "case when amount is null then 0 else amount end");
        finish = finish.updateField("freezebalance", "case when freezebalance is null then 0 else freezebalance end");
        finish = finish.updateField("unrestrictedamt", "case when unrestrictedamt is null then 0 else unrestrictedamt end");
        finish = finish.updateField("surplusamount", "case when surplusamount is null then 0 else surplusamount end");

        finish = finish.addField("nckd_depositbalance + amount + unrestrictedamt + surplusamount", "totalfunds");
        finish = finish.addField("freezebalance + unrestrictedamt1 + surplusamount1", "restrictedfunds");
        finish = finish.addField("totalfunds - restrictedfunds", "availablefunds");
        //finish = finish.addField("0", "sort");
        log.info("finish1:");
        finish1.print(true);
        log.info("finish2:");
        finish2.print(true);
        log.info("finish3:");
        finish3.print(true);
        log.info("finish4:");
        finish4.print(true);
        log.info("finish:");
        finish.print(true);
        finish = finish.filter("totalfunds > 0 or restrictedfunds > 0 or availablefunds > 0");
        log.info("过滤后的finish:");
        finish.print(true);
        //资金总额穿透明细表，前三个公司顺序固定，分别为：建材集团、股份本部、南方本部。其他公司顺序无要求
        String[] strArr = {"C0", "C01", "C010301"};
        QFilter orgFilter = new QFilter("number", QCP.in, strArr);
        DynamicObject[] bosOrgs = BusinessDataServiceHelper.load("bos_org", "id,number,name", orgFilter.toArray());
        Map<String, Long> orgMap = Arrays.stream(bosOrgs).collect(Collectors.toMap(k -> k.getString("number"), v -> v.getLong("id")));
        //处理finish 按照建材集团、股份本部、南方本部。其他公司排序
        DataSet C0DataSet = finish.copy().filter("company = " + orgMap.get("C0"));
        C0DataSet = C0DataSet.addField("1", "sort").select("company", "bankaccountnumber", "bankbankcate", "totalfunds", "restrictedfunds", "availablefunds", "sort");
        DataSet C01DataSet = finish.copy().filter("company = " + orgMap.get("C01"));
        C01DataSet = C01DataSet.addField("3", "sort").select("company", "bankaccountnumber", "bankbankcate", "totalfunds", "restrictedfunds", "availablefunds", "sort");
        DataSet C010301DataSet = finish.copy().filter("company = " + orgMap.get("C010301"));
        C010301DataSet = C010301DataSet.addField("5", "sort").select("company", "bankaccountnumber", "bankbankcate", "totalfunds", "restrictedfunds", "availablefunds", "sort");
        DataSet otherDataSet = finish.copy().filter("company not in  (" + orgMap.get("C0") + "," + orgMap.get("C01") + "," + orgMap.get("C010301") + ")");
        otherDataSet = otherDataSet.addField("7", "sort").select("company", "bankaccountnumber", "bankbankcate", "totalfunds", "restrictedfunds", "availablefunds", "sort");

        //DataSet details = C0DataSet.union(C01DataSet).union(C010301DataSet).union(otherDataSet).select("company", "bankaccountnumber", "bankbankcate", "totalfunds", "restrictedfunds", "availablefunds", "sort");
        DataSet total = finish.groupBy(new String[]{"company"}).sum("totalfunds").sum("restrictedfunds").sum("availablefunds").finish();
        DataSet C0TotalDataSet = total.copy().filter("company = " + orgMap.get("C0"));
        C0TotalDataSet = C0TotalDataSet.addField("2", "sort").addNullField("bankbankcate").addField("'小计'", "bankaccountnumber")
                .select("company", "bankaccountnumber", "bankbankcate", "totalfunds", "restrictedfunds", "availablefunds", "sort");
        DataSet C01TotalDataSet = total.copy().filter("company = " + orgMap.get("C01"));
        C01TotalDataSet = C01TotalDataSet.addField("4", "sort").addNullField("bankbankcate").addField("'小计'", "bankaccountnumber")
                .select("company", "bankaccountnumber", "bankbankcate", "totalfunds", "restrictedfunds", "availablefunds", "sort");
        DataSet C010301TotalDataSet = total.copy().filter("company = " + orgMap.get("C010301"));
        C010301TotalDataSet = C010301TotalDataSet.addField("6", "sort").addNullField("bankbankcate").addField("'小计'", "bankaccountnumber")
                .select("company", "bankaccountnumber", "bankbankcate", "totalfunds", "restrictedfunds", "availablefunds", "sort");
        DataSet otherTotalDataSet = total.filter("company not in  (" + orgMap.get("C0") + "," + orgMap.get("C01") + "," + orgMap.get("C010301") + ")");
        otherTotalDataSet = otherTotalDataSet.addField("8", "sort").addNullField("bankbankcate").addField("'小计'", "bankaccountnumber")
                .select("company", "bankaccountnumber", "bankbankcate", "totalfunds", "restrictedfunds", "availablefunds", "sort");
        //DataSet totalDataSet = C0TotalDataSet.union(C01TotalDataSet).union(C010301TotalDataSet).union(otherTotalDataSet).select("company", "totalfunds", "restrictedfunds", "availablefunds", "sort");

        DataSet result = otherDataSet.union(otherTotalDataSet).orderBy(new String[]{"company", "sort"});
        //result = result.filter("totalfunds > 0 or restrictedfunds > 0 or availablefunds > 0");
        DataSet union = C0DataSet.union(C0TotalDataSet).union(C01DataSet).union(C01TotalDataSet).union(C010301DataSet).union(C010301TotalDataSet).union(result);

//        DynamicObject test = BusinessDataServiceHelper.newDynamicObject("nckd_testdocuments");
//        test.set("billno","test-A-" + new SimpleDateFormat("yyyyMMddHHmm").format(new Date()));
//        test.set("billstatus","A");
//        DynamicObjectCollection entryentity = test.getDynamicObjectCollection("entryentity");
//        //打印单据
//        DataSet recCopy = union.copy();
//        while (recCopy.hasNext()) {
//            DynamicObject entry = entryentity.addNew();
//            Row row = recCopy.next();
//            entry.set("nckd_name", row.getString("restrictedfunds")+ "," + row.getString("availablefunds"));
//            entry.set("nckd_number", row.getString("company") + "," + row.getString("totalfunds"));
//            entry.set("nckd_amount", 0);
//            entry.set("nckd_text",row.get("sort"));
//        }
//        SaveServiceHelper.save(new DynamicObject[]{test});

        return union;
    }

    /**
     * 获取"1502209019300176348", "791902491410301", "14314101040028653", "36050153017100003058" 四个归集账户的子账户的归集总额
     *
     * @return
     */
    private DataSet getParentBankAccountNumberAmount(DataSet beiBankbalanceDataSetCopy) {
        //母子账户组 fca_acctgroup
        String fcaAcctgroupSelectField = "id,company.id companyid,company.number companynumber,company.name companyname,accountbank.id accountbankid,accountbank.bankaccountnumber parentcompanyaccount, " +
                "entrys.bankacct.id bankacctid,entrys.bankacct.bankaccountnumber subsidiarybankaccount,entrys.bankacct.company.id bankacctcompanyid,entrys.bankacct.company.number bankacctcompanynumber,entrys.bankacct.company.name bankacctcompanyname";
        QFilter fcaacctgroupFilter = new QFilter("enable", QCP.equals, "1");
        //有效的母子账户组
        DataSet fcaAcctgroupDataSet = QueryServiceHelper.queryDataSet(this.getClass().getName(), "fca_acctgroup", fcaAcctgroupSelectField, fcaacctgroupFilter.toArray(), null);

        DataSet finish = fcaAcctgroupDataSet.join(beiBankbalanceDataSetCopy).on("subsidiarybankaccount", "bankaccountnumber")
                .select(new String[]{"parentcompanyaccount"}, new String[]{"nckd_depositbalance"})
                .finish().groupBy(new String[]{"parentcompanyaccount"}).sum("nckd_depositbalance").finish();

        return finish;
    }
}
